<?php
require_once('MySqlConnection.php');
require_once('Location.php');
class LocationDAL{ 
	private $_connection;
	//Singleton pattern
    private static $_instance = null;
	public static function Instance(){
		if (self::$_instance == null) self::$_instance = new LocationDAL();
		return self::$_instance;
	}
	private function __construct(){
		include_once('./include/db.inc');
		$this->_connection = MySqlConnection::Instance($server,$user,$password,$database);
	}
	
	public function getLocations(){
		$this->_connection->Open();
		$sqlString = "select * from location";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'location');
	}
	
	public function getLocationsWithPracticesByDistrict($districtId){
		$this->_connection->Open();
		$sqlString = "SELECT * FROM (select l.*, (SELECT COUNT(*) FROM address a JOIN practice p ON p.address_id = a.id JOIN user u ON p.user_id = u.id WHERE a.location_id = l.id AND u.activated = 1) as number_of_practices from location l where district_id = '{$districtId}') distrs WHERE number_of_practices > 0";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'location');
	}
	
	public function getLocationsByDistrict($districtId){
		$this->_connection->Open();
		$sqlString = "select l.*, (SELECT COUNT(*) FROM address a JOIN practice p ON p.address_id = a.id WHERE a.location_id = l.id) as number_of_practices from location l where district_id = '{$districtId}'";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'location');
	}
	
	public function getLocationsByCountry($countryId){
		$this->_connection->Open();
		$sqlString = "select * from location l join district d on l.district_id = d.district_id where d.country_id = '{$countryId}' ORDER BY postal_number";
		$resultData = $this->_connection->ExecuteSQL($sqlString, 'O');
		return $this->Mapping($resultData, 'location');
	}
	
	public function addLocation($location){
		include('./include/db.inc');
		$mysqli = new mysqli($server, $user, $password, $database);
		if(mysqli_connect_errno()) {
		  echo "Connection Failed: " . mysqli_connect_errno();
		  exit();
		}
	    if($stmt = $mysqli->prepare("INSERT INTO location(district_id, postal_number, city_name, alpha, longitude, latitude) VALUES(?,?,?,?,?,?)")) {
			$stmt->bind_param("iissdd", $location['district_id'], $location['postal_number'], $location['city_name'],$location['alpha'],$location['longitude'],$location['latitude']);
			$success = $stmt->execute();
			if(!$success){
				echo "Inserting location failed!";
			}
		}
		else{
			
			echo "Failed preparing insert location statement!";
		}
		$mysqli -> close();
	}
	
	//MAPPERS
	private function Mapping($queryData,$resultType){
		foreach($queryData as $data){
			$resultArray[] = $this->Map($data,$resultType);
		}
		return $resultArray;
	}
	private function Map($data,$resultType){
		$object = new Location($data->id,$data->district_id,$data->postal_number,$data->city_name, $data->alpha, $data->longitude, $data->latitude, $data->number_of_practices);
		return $object;
	}
}